Steven Dutch, Professor Emeritus, Natural and Applied Sciences, Universityof Wisconsin - Green Bay
Information on the UTM system
Formulas for UTM Conversion
Spreadsheet For UTM Conversion
This spreadsheet employs formulas by Charles Karney (2010). The formulas are accurate to nanometers, even 80 degrees from the central meridian. Excel's precision of 15 digits means that the spreadsheet is accurate to about 0.01 mm. Also, the formulas can blow up at low latitudes beyond 80 degrees from the central meridian. That's far more than sufficient for just about all purposes.
Also, consider the accuracy of your data. A degree is about 111,000 meters. Thus, to achieve roughly one-meter accuracy you need coordinates accurate to five decimal places. Four places will give you 10 meters, three will give you 100 meter accuracy.
This is a spreadsheet, not an applet or a program. Unlike a text box in Visual Basic, I do not know of any way to make a cell capable of accepting input and at the same time change its contents in response to another cell. Anything you input into a cell will overwrite any existing formulas. Therefore, if you input decimal latitude and longitude, the dd.mm.ss input boxes will not change. That can't be done in a spreadsheet, so don't expect to see it. There is a display area below that shows the conversions.
In response to one frustrated user: there is no way to get latitude and longitude from UTM coordinates if you do not know your longitude zone and hemisphere. There are four places on earth that have the same latitude and longitude if you omit N/S and E/W. There are 120 places that have the same northing and easting. If you have simply been given a bunch of numbers to convert, you need to go back to whomever collected the data and get the complete information. This spreadsheet cannot do it for you.
On the other hand, if you know approximately where the data comes from, the spreadsheet will tell you. Enter the approximate latitude and longitude into the spreadsheet. Ignore the northing and easting values and simply read off the zone.
The spreadsheet consists of seven sheets, three of which are normally available for input (bold).:
For most purposes you will only need to visit the Main and Batch Convert pages. I recommend that you save the spreadsheet, then load it for use but do not save it. That way, if you wreck the spreadsheet accidentally, you will still have an uncorrupted version. Do not modify any of the other pages. For the most part, input on the main page is restricted to white cells.
Pretty self explanatory. Select the datum used on your map in the upper left corner.
To convert latitude and longitude to UTM, you can either use decimal format or dd.mm.ss format. Use the buttons to clear data and select decimal or DMS input mode. North and east are positive, south and west are negative. Use only signs to designate hemispheres - do not use "N" or "S." If you are inputting DMS coordinates, only use minus signs on the degrees. You do not (and in fact cannot) need to input negative minutes and seconds.
As noted above, do not expect to see conversions in the data entry cells.Geographic coordinates in both decimal and dd.mm.ss format will appear in the colored result area beneath.
To convert UTM to latitude and longitude, you must enter northing, easting, the longitude zone and whether your latitude is north or south. If you don't enter a zone, the default is zone 31 (0-6 degrees east).
If you don't know the zone, but do have a rough idea of the location, enter an approximate latitude and longitude into the section for converting to UTM. Ignore the northing and easting values and read the zones. Then enter the zone into the section for converting UTM to geographic coordinates.
Enter the Longitude Zone, Latitude Zone, Digraph, Easting and Northing in the appropriate cells. Read the global UTM and geographic coordinates in the light green boxes.
To use this page, you need to know how to copy formulas on a spreadsheet. If your data is already in decimal format, copy it into columns D and E. If you have dd.mm.ss data, you will need to convert it. Enter the longitude zone in Column G and the longitude of the central meridian in Column H.
Copy the formulas in columns H-V (pink) down to the bottom of your data. The columns are minimized in width but still visible so you can see what to copy. Input the false easting in column W.
For standard UTM, it is possible to calculate the zone and central meridian, and false easting is always 500,000. On this page, those quantities must be manually entered, since you may want to use a non-standard meridian or calculate grid coordinates outside the standard zone.
To use this page, you need to know how to copy formulas on a spreadsheet.
Enter your hemisphere, longitude zone, northing and easting in columns D-G. For standard UTM, it is possible to calculate the zone and central meridian, and false easting is always 500,000. On this page, those quantities must be manually entered, since you may want to use a non-standard meridian or calculate grid coordinates outside the standard zone. Copy the formulas in columns H-AA down as far as necessary to do the conversions. Latitude and Longitude are given in dd.dddd format in columns AB and AC, and in dd.mm.ss format in columns AD-AI.
Yay! Most of these are on-line now.
Snyder, J. P., 1987; Map Projections - A Working Manual. U.S. GeologicalSurvey Professional Paper 1395, 383 p. If you are at all serious about mapsyou need this book. On-line athttp://pubs.er.usgs.gov/publication/pp1395
Army, Department of, 1973; Universal Transverse Mercator Grid, U. S. ArmyTechnical Manual TM 5-241-8, 64 p. Superseded by DMATM 8358.2 The Universal Grids: Universal Transverse Mercator (UTM) and Universal Polar Stereographic (UPS).
On-line athttp://earth-info.nga.mil/gandG/publications/tm8358.2/TM8358_2.pdf
NIMA Technical Report 8350.2, "Department of Defense World GeodeticSystem 1984, Its Definition and Relationships with Local Geodetic Systems,"Second Edition, 1 September 1991 and its supplements. The report is availablefrom the NIMA Combat Support Center and its stock number is DMATR83502WGS84.Non-DoD requesters may obtain the report as a public sale item from the U.S.Geological Survey, Box 25286, Denver Federal Center, Denver, Colorado 80225 orby phone at 1-800-USA-MAPS. On-line athttp://earth-info.nga.mil/gandG/publications/tr8350.2/wgs84fin.pdf
Karney, Charles F.F., 2010, Transverse Mercator with an accuracy of a few nanometers, http://arxiv.org/abs/1002.1417v3. Provides accuracy to within nanometers (a few atoms). Supersedes Snyder and Army, above. http://geographiclib.sourceforge.net provides links to numerous utilities useful in analyzing the ellipsoidal figure of the earth.
Return to Professor Dutch's Home Page
Created 18 April 2005, Last Update 24 May 2020